﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SqlShareAPI
{
    /// <summary>
    /// User Session can be obtained via SSAuthentication
    /// </summary>
    public class UserSession
    {

        /// <summary>
        /// Token for this session. This is just meta data that will allow the user to use the 'token' to automatically
        /// revisit this session without providing a password. This token does not ensure that this exact object will exist
        /// when 'IsLoggedIn'
        /// </summary>
        public string Token { get; private set; }
        public string UserName { get; private set; }
        public string FullName { get; private set; }
        public bool IsAdmin { get; private set; }        

        private SSAuthentication _auth;
        private SSSqlConnection _conn;


        internal UserSession(string username, string token, string fullname, bool isAdmin, SSAuthentication auth,  SSSqlConnection conn)
        {
            UserName = username;
            Token = token;
            FullName = fullname;
            IsAdmin = isAdmin;
            _auth = auth;
            _conn = conn;
            _conn.SetUserSession(this);
        }        

        public UserQuery SaveUserQuery(string shortDesc, string longDesc, string sqlCode, bool isPublic)
        {            
            // first create the record
            SqlCommand cmd = new SqlCommand("sp_query_modify", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            cmd.Parameters.Add("@sql_code", SqlDbType.VarChar).Value = sqlCode;
            cmd.Parameters.Add("@short_desc", SqlDbType.VarChar).Value = shortDesc;
            cmd.Parameters.Add("@long_desc", SqlDbType.VarChar).Value = longDesc;
            cmd.Parameters.Add("@is_public", SqlDbType.Bit).Value = isPublic;

            SqlDataReader r = cmd.ExecuteReader();
            r.Read();
            UserQuery query = new UserQuery(
                r["owner"].ToString(),
                r["short_desc"].ToString(),
                r["long_desc"].ToString(), 
                r["sql_code"].ToString(), 
                bool.Parse(r["is_public"].ToString()));
            r.Close();

            return query;
        }

        /// <summary>
        /// Attempt to create a new user 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool AdminCreateUser(string username, string password, string fullname, bool asAdmin)
        {
            return _auth.AdminCreateUser(username, password, fullname, asAdmin);
        }

        /// <summary>
        /// The content connection to query into user space
        /// </summary>
        public SqlConnection ContentSqlConnection
        {
            get { return _conn.UserContentConnection; }
        }

        /// <summary>
        /// Set the permissions of the table
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="isPublic"></param>
        private void SetTablePermission(string tablename, bool isPublic)
        {
            // going to need to strip table [ ]'s if they exist since this is a stored procedure
            if (tablename.StartsWith("[") && tablename.EndsWith("]"))
            {
                tablename = tablename.Substring(1, tablename.Length - 2);
            }
            SqlCommand cmd = new SqlCommand("sp_table_permissions_set", _conn.UserContentConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = tablename;
            cmd.Parameters.Add("@is_public", SqlDbType.Bit).Value = isPublic;
            cmd.ExecuteNonQuery();
        }

        public List<UserTable> GetAllTables()
        {
            List<UserTable> list = new List<UserTable>();

            SqlCommand cmd = new SqlCommand("sp_table_get_all", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            SqlDataReader r = cmd.ExecuteReader();

            while (r.Read())
            {
                list.Add(new UserTable(
                    r["owner"].ToString(),
                    r["name"].ToString(),
                    r["description"].ToString(),
                    bool.Parse(r["is_view"].ToString()),
                    bool.Parse(r["is_public"].ToString())
                    ));
            }
            r.Close();

            return list;
        }

        /// <summary>
        /// Get all queries that are viewable to this user
        /// </summary>
        /// <returns></returns>
        public List<UserQuery> GetAllQueries()
        {
            List<UserQuery> list = new List<UserQuery>();

            SqlCommand cmd = new SqlCommand("sp_query_get_all", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            SqlDataReader r = cmd.ExecuteReader();

            while (r.Read())
            {
                list.Add(new UserQuery(
                    r["owner"].ToString(),
                    r["short_desc"].ToString(),
                    r["long_desc"].ToString(),
                    r["sql_code"].ToString(),
                    bool.Parse(r["is_public"].ToString())
                ));
            }
            r.Close();

            return list;
        }

        /// <summary>
        /// Closes underlieing SQL Connection, dont do this unless you are done making any queries without launchign an ew session
        /// </summary>
        public void Close()
        {
            _conn.CloseAll();
        }

        /// <summary>
        /// IMPORTANT Note: this does not create the table, only the meta entry and sets permissions if neccessary.
        /// 
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="description"></param>
        /// <param name="isPublic"></param>
        /// <param name="isView"></param>
        /// <returns></returns>
        public UserTable CreateTable(string tablename, string description, bool isPublic, bool isView)
        {
            // first apply new rules to main server
            SetTablePermission(tablename, isPublic);

            // update our meta entry
            SqlCommand cmd = new SqlCommand("sp_table_create", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = tablename;
            cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = description;
            cmd.Parameters.Add("@is_public", SqlDbType.Bit).Value = isPublic;
            cmd.Parameters.Add("@is_view", SqlDbType.Bit).Value = isView;

            SqlDataReader r = cmd.ExecuteReader();
            r.Read();

            UserTable table = new UserTable(
                r["owner"].ToString(),
                r["name"].ToString(),
                r["description"].ToString(),
                bool.Parse(r["is_view"].ToString()),
                bool.Parse(r["is_public"].ToString()));
            r.Close();

            return table;
        }

        /// <summary>
        /// Delete the user query
        /// </summary>
        /// <param name="shortDescription"></param>
        public void DeleteUserQuery(string shortDescription)
        {
            SqlCommand cmd = new SqlCommand("sp_query_delete", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@short_desc", SqlDbType.VarChar).Value = shortDescription;
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;

            cmd.ExecuteNonQuery();
        }

        public void DeleteUserTable(string tablename)
        {
            // first try delete from content database, because we might fail there due to
            // some other view using it as a dependency
            SqlCommand cmd = new SqlCommand("DROP TABLE ["+tablename+"]", ContentSqlConnection);
            cmd.ExecuteNonQuery();

            cmd = new SqlCommand("sp_table_delete", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@tablename", SqlDbType.VarChar).Value = tablename;
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Save the settings of this table
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="desc"></param>
        /// <param name="isPublic"></param>
        /// <returns></returns>
        public UserTable SaveUserTable(string tablename, string desc, bool isPublic)
        {
            // first apply new rules to main server            
            SetTablePermission(tablename, isPublic);

            ///
            SqlCommand cmd = new SqlCommand("sp_table_modify", _conn.MetaConnection);
            cmd.CommandType = CommandType.StoredProcedure;            
            cmd.Parameters.Add("@owner", SqlDbType.VarChar).Value = UserName;
            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = tablename;
            cmd.Parameters.Add("@desc", SqlDbType.VarChar).Value = desc;
            cmd.Parameters.Add("@is_public", SqlDbType.Bit).Value = isPublic;

            SqlDataReader r = cmd.ExecuteReader();
            r.Read();

            UserTable table = new UserTable(
                r["owner"].ToString(),
                r["name"].ToString(),
                r["description"].ToString(),
                bool.Parse(r["is_view"].ToString()),
                bool.Parse(r["is_public"].ToString()));
            r.Close();

            return table;
        }

        /// <summary>
        /// Get a serializable version of this data
        /// </summary>
        public UserData User { get { return new UserData(UserName, FullName, IsAdmin); } }
    }

}
